library(here)
library(tidyverse)
library(lubridate)
library(forcats)
library(kableExtra)
library(countrycode) # to code region below
library(broom)
library(janitor)
library(kableExtra)
library(ggridges)
library(usethis) # new

knitr::opts_chunk$set(echo = FALSE, warning=FALSE, message = FALSE, fig.width = 6)
# filter out columns with all NA rows
 # remove_empty_cols() %>%
  # remove_empty_rows()

theme_set(theme_minimal())
apsrcolors8 = c("#646768", "#E0003C", "#F89012", "#F7B926", "#74A94C", "#1F9898", "#1082A8", "#924EA6", "#E00069")
apsrcolors4 = c("#646768", "#E0003C", "#F7B926", "#74A94C")
# apsrcolors2 = c("#646768", "#1082A8")
apsrcolors2 = c("#646768", "#afd4e0")
apsrcolors2x2 = c("#646768", "#1082A8","#646768", "#1082A8")
apsrlines2x2 = c("solid","solid", "dotdash", "dotdash")
apsrcolors3x3 = c("#646768", "#1082A8", "#E0003C", "#646768", "#1082A8", "#E0003C")
apsrlines3x3 = c("solid","solid", "solid", "dotdash", "dotdash", "dotdash")
# 4

# apsrcolors8 = c("#E0003C", "#646768", "#E00069", "#924EA6", "#1082A8", "#1F9898", "#74A94C", "#F7B926", "#F89012")

options(knitr.kable.NA = '')

# important dates
start_date = date("2018-01-01")
end_date = date("2022-10-31")

lastyear_start_date = date("2021-06-01")
lastyear_end_date = date("2022-05-31")

currentyear_start_date = date("2022-06-01")
currentyear_end_date = date("2023-05-31")

current_team_start_date = date("2020-06-01")
# format(current_team_start_date, format="%m/%d/%Y")

end_date_year = year(end_date)

# sub.all = read_csv(here("data","sub.all.csv"), guess_max = 20000)
sub.all <- readRDS(here("data","sub.all.rds"))
# exclude those submitted after end_date 
sub.all = sub.all %>%
  filter(date_rev_sub_corrected <= end_date) %>%
  filter(date_rev_sub_corrected >= start_date) 
# make complete df list of dates for time period
days <- as.data.frame(seq(from = start_date, to = end_date, by = 'day'))
colnames(days) <- "date_rev_sub_corrected"

# df of only new submissions 
newsubs = sub.all %>%
  filter(revision == 0) 


###################################################
# New and Total Completed Submissions
###################################################
# This code only includes submissions that enter workflow
# and receive an editor's decision. Coding is consistent 
# across teams. 
###################################################
# Note that Mannheim team code used "first receipt date" & 
# may have miscounted submissions when a particular manuscript
# was resubmitted more than once in a year
###################################################


sub.all %>%
  filter(revision == 0) %>%
  relocate(man_num, revision, date_rev_sub_corrected) %>%
  mutate(year = year(date_rev_sub_corrected), 
         month = month(date_rev_sub_corrected)) %>%
  group_by(year, month) %>%
  summarize(n = n()) %>%
  write_csv(here("data", "submissions_byyearmonth.csv"))



classification = newsubs %>%
  mutate(team = droplevels(team)) %>%
  select(team, classification_description, number_of_authors, author_team_id, author_team_eth, a_n_nonbin_id, a_n_latinx, a_n_asian, a_n_other, a_n_indig, a_n_black, a_n_mideast, a_n_eth_dna, corr_author_region3) 

classification = classification %>%
  mutate(Race = if_else(str_detect(classification_description, "Race"), "Race/Ethnicity", "Not"), 
         Race = if_else(str_detect(classification_description, "Ethnicity"), "Race/Ethnicity", Race), 
         Race = if_else(str_detect(classification_description, "Racial"), "Race/Ethnicity", Race), 
         Race = if_else(str_detect(classification_description, "Ethnic"), "Race/Ethnicity", Race), 
         Race = if_else(str_detect(classification_description, "African American Politics"), "Race/Ethnicity", Race), 
         Race = if_else(str_detect(classification_description, "Latina"), "Race/Ethnicity", Race), 
         Race = if_else(str_detect(classification_description, "Asian Pacific American Politics"), "Race/Ethnicity", Race), 
         Race = if_else(str_detect(classification_description, "Native American Politics"), "Race/Ethnicity", Race), 
         GenderSex = if_else(str_detect(classification_description, "Gender"), "Women/\nGender/\nSexuality", "Not"), 
         GenderSex = if_else(str_detect(classification_description, "Women"), "Women/\nGender/\nSexuality", GenderSex), 
         GenderSex = if_else(str_detect(classification_description, "Gender"), "Women/\nGender/\nSexuality", GenderSex), 
         GenderSex = if_else(str_detect(classification_description, "Gay"), "Women/\nGender/\nSexuality", GenderSex), 
         GenderSex = if_else(str_detect(classification_description, "Lesbian"), "Women/\nGender/\nSexuality", GenderSex), 
         Environment = if_else(str_detect(classification_description, "Environment"), "Environment", "Not"), 
         Developing = if_else(str_detect(classification_description, "Developing Nations"), "Developing", "Not"), 
         Africa = if_else(str_detect(classification_description, "Africa"), "Africa", "Not"), 
         LAC = if_else(str_detect(classification_description, "South America"), "LAC", "Not"), 
         LAC = if_else(str_detect(classification_description, "Caribbean"), "LAC", LAC),
         LAC = if_else(str_detect(classification_description, "Latin America"), "LAC", LAC),
         LAC = if_else(str_detect(classification_description, "Central America"), "LAC", LAC), 
         MidEast = if_else(str_detect(classification_description, "Middle East"), "Middle East", "Not"), 
         SAsia = if_else(str_detect(classification_description, "South Asia"), "South Asia", "Not"), 
         SEAsia = if_else(str_detect(classification_description, "Southeast Asia"), "Southeast Asia", "Not"))
classification_tab = classification %>%
  group_by(team) %>%
  summarize(`Race/\nEthnicity` = sum(Race == "Race/Ethnicity"), 
            `Women/\nGender/\nSexuality` = sum(GenderSex == "Women/\nGender/\nSexuality"), 
            `Environ-\nment` = sum(Environment == "Environment"), 
            LDCs = sum(Developing == "Developing"), 
            Africa = sum(Africa == "Africa"), 
            `Middle\nEast` = sum(MidEast == "Middle East"), 
            LAC = sum(LAC == "LAC"), 
            `South\nAsia` = sum(SAsia == "South Asia"), 
            `SE\nAsia` = sum(SEAsia == "Southeast Asia")) %>%
  pivot_longer(!team, names_to = "Classification", values_to = "Count") %>%
  mutate(Classification = as_factor(Classification), 
         Classification = fct_relevel(Classification, "Race/\nEthnicity", "Women/\nGender/\nSexuality")) %>%
  filter(team == "Current" | team == "Mannheim")
write_csv(classification_tab, here("data", "classification_tab.csv"))



methodology = newsubs %>%
  # filter(!is.na(methodology)) %>%
  filter(team == "Mannheim" | team == "Current") %>%
  mutate(team = droplevels(team), 
         methodology = fct_recode(methodology, "Statistical-\nObservational" = "Statistical-Observational",
                                  "Case study or\nSmall N" = "Ethnographic", 
                                  "Case study or\nSmall N" = "Case study/Small N", 
                                  "Experimental" = "Experimental (lab, survey, or field)", 
                                  "Interpretive,\nCritical, or\nPoststructural" = "Critical Theory/Poststructuralist", 
                                  "Interpretive,\nCritical, or\nPoststructural" = "Interpretive"), 
         methodology = fct_relevel(methodology, "Statistical-\nObservational", 
                                   "Experimental", 
                                   "Interpretive,\nCritical, or\nPoststructural", 
                                   "Normative")) %>%
  select(team, methodology)
  
methodology_tab = methodology %>%
  tabyl(methodology, team)
write_csv(methodology_tab, here("data", "methodology_tab.csv"))



corr_author_region3 = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  mutate(team = droplevels(team), 
         corr_author_region3 = fct_relevel(corr_author_region3, "N. America", "Europe")) %>%
  select(team, corr_author_region3)

corr_author_region3_tab = corr_author_region3 %>%
  tabyl(corr_author_region3, team)

write_csv(corr_author_region3_tab, here("data", "corr_author_region3_tab.csv"))



gender = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  mutate(team = fct_drop(team), 
         author_team_id = if_else(a_n_nonbin_id == 1 & author_team_id == "solo uncoded", "solo non-binary", author_team_id), 
         author_team_id = if_else(a_n_nonbin_id > 0 & author_team_id == "uncoded team", "mixed team", author_team_id), 
         author_team_id = if_else(number_of_authors==1 & is.na(author_team_id), "solo uncoded", author_team_id), 
         author_team_id = if_else(number_of_authors>1 & is.na(author_team_id), "uncoded team", author_team_id), 
         author_team_id = fct_recode(author_team_id, 
                                     "All woman\nteam" = "team of women", 
                                     "All man\nteam" = "team of men", 
                                     "Mixed\nteam" = "mixed team", 
                                     "Solo\nwoman" = "solo woman", 
                                     "Solo\nman" = "solo man", 
                                     "Solo\nnon-binary" = "solo non-binary",
                                     "Uncoded\nteam" = "uncoded team", 
                                     "Solo\nuncoded" = "solo uncoded"), 
         author_team_id = fct_relevel(author_team_id, "Solo\nman", "Solo\nwoman", "Solo\nnon-binary", "All man\nteam", "Mixed\nteam", "All woman\nteam", "Solo\nuncoded", "Uncoded\nteam")) %>%
  select(team, author_team_id)
write_csv(gender, here("data", "gender.csv"))
nonbinary = sub.all %>%
  filter(revision == 0) %>%
  filter(team == "Mannheim" | team == "Current") %>%
  select(a_n_nonbin_id, team) %>%
  filter(a_n_nonbin_id >0) %>%
  group_by(team) %>%
  summarize(nonbinary = sum(a_n_nonbin_id))
write_csv(nonbinary, here("data", "nonbinary.csv"))

nonbin_uncoded = sub.all %>%
  filter(revision == 0) %>%
  filter(team == "Mannheim" | team == "Current") %>%
  filter(str_detect(author_team_id, "uncoded")) %>%
  filter(a_n_nonbin_id >0) %>%
  group_by(author_team_id) %>%
  summarize(nonbinary = n())
write_csv(nonbin_uncoded, here("data", "nonbin_uncoded.csv"))



race = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  mutate(team = droplevels(team),
         author_team_eth = if_else(number_of_authors == 1 & is.na(author_team_eth), "Solo,\ndeclined", author_team_eth), 
         author_team_eth = if_else(number_of_authors > 1 & is.na(author_team_eth), "Team,\n1+\ndeclined", author_team_eth), 
         author_team_eth = fct_recode(author_team_eth, 
                                      "Solo,\nauthor\nof color" = "Solo, author of color", 
                                      "Team,\n1+\nauthors\nof color" = "Team, 1+ authors of color", 
                                      "Team,\nall\nauthors\nof color" = "Team, all authors of color", 
                                      "Team,\nall\nwhite\nauthors" = "Team, all white authors", 
                                      "Solo,\nwhite\nauthor" = "Solo, white author", 
                                      "Solo,\ndeclined" = "Solo, declined", 
                                      "Team,\n1+\ndeclined" = "Team, 1+ declined"), 
         author_team_eth = fct_relevel(author_team_eth, "Solo,\nwhite\nauthor", "Team,\nall\nwhite\nauthors", 
                                       "Solo,\nauthor\nof color", "Team,\n1+\nauthors\nof color",
                                       "Team,\nall\nauthors\nof color")) %>%
  select(team, author_team_eth)
write_csv(race, here("data", "race.csv"))



classification_inter = classification %>%
  filter(team == "Mannheim" | team == "Current") %>%
  mutate(team = fct_drop(team), 
         author_team_id = as.character(author_team_id), 
         author_team_id = if_else(a_n_nonbin_id == 1 & author_team_id == "solo uncoded", "solo non-binary", author_team_id), 
         author_team_id = if_else(a_n_nonbin_id > 0 & author_team_id == "uncoded team", "mixed team", author_team_id), 
         author_team_id = if_else(number_of_authors ==1 & is.na(author_team_id), "solo uncoded", author_team_id), 
         author_team_id = if_else(number_of_authors > 1 & is.na(author_team_id), "uncoded team", author_team_id))

classification_inter = classification_inter %>%
  mutate(author_team_id = as_factor(author_team_id), 
         author_team_id = fct_recode(author_team_id, 
                                     "All woman\nteam" = "team of women", 
                                     "All man\nteam" = "team of men", 
                                     "Mixed\nteam" = "mixed team", 
                                     "Solo\nwoman" = "solo woman", 
                                     "Solo\nman" = "solo man", 
                                     "Solo\nnon-binary" = "solo non-binary",
                                     "Uncoded\nteam" = "uncoded team", 
                                     "Solo\nuncoded" = "solo uncoded"), 
         author_team_id = fct_relevel(author_team_id, "Solo\nman", "Solo\nwoman", "Solo\nnon-binary", "All man\nteam", "Mixed\nteam", "All woman\nteam", "Solo\nuncoded", "Uncoded\nteam"), 
         author_team_eth = if_else(number_of_authors == 1 & is.na(author_team_eth), "Solo,\ndeclined", author_team_eth), 
         author_team_eth = if_else(number_of_authors > 1 & is.na(author_team_eth), "Team,\n1+\ndeclined", author_team_eth), 
         author_team_eth = fct_recode(author_team_eth, 
                                      "Solo,\nauthor\nof color" = "Solo, author of color", 
                                      "Team,\n1+\nauthors\nof color" = "Team, 1+ authors of color", 
                                      "Team,\nall\nauthors\nof color" = "Team, all authors of color", 
                                      "Team,\nall\nwhite\nauthors" = "Team, all white authors", 
                                      "Solo,\nwhite\nauthor" = "Solo, white author", 
                                      "Solo,\ndeclined" = "Solo, declined", 
                                      "Team,\n1+\ndeclined" = "Team, 1+ declined"), 
         author_team_eth = fct_relevel(author_team_eth, "Solo,\nwhite\nauthor", "Team,\nall\nwhite\nauthors", 
                                       "Solo,\nauthor\nof color", "Team,\n1+\nauthors\nof color",
                                       "Team,\nall\nauthors\nof color"),
         author_team_eth_basic = fct_recode(author_team_eth, 
                                  "White\nauthor(s)" = "Solo,\nwhite\nauthor", 
                                  "White\nauthor(s)" = "Team,\nall\nwhite\nauthors", 
                                  "1+\nAuthor(s)\nof color" = "Solo,\nauthor\nof color", 
                                  "1+\nAuthor(s)\nof color" = "Team,\n1+\nauthors\nof color",
                                  "1+\nAuthor(s)\nof color" = "Team,\nall\nauthors\nof color", 
                                  "Declined" = "Solo,\ndeclined", 
                                  "Declined" = "Team,\n1+\ndeclined"), 
         author_team_eth_basic = replace_na(author_team_eth_basic, "Declined"), 
         author_team_gen_basic = fct_recode(author_team_id, 
                                            "Men" = "Solo\nman", 
                                            "Women/\nNon-binary" = "Solo\nwoman", 
                                            "Women/\nNon-binary" = "Solo\nnon-binary", 
                                            "Men" = "All man\nteam", 
                                            "Women/\nNon-binary" = "All woman\nteam", 
                                            "Declined" = "Solo\nuncoded", 
                                            "Declined" = "Uncoded\nteam"), 
         GlobalSouth = case_when(Developing == "Developing" ~ "Global South", 
                                 Africa == "Africa" ~ "Global South", 
                                 LAC == "LAC" ~ "Global South", 
                                 MidEast == "Middle East" ~ "Global South", 
                                 SAsia == "South Asia" ~ "Global South", 
                                 SEAsia == "SE Asia" ~ "Global South", 
                                 TRUE ~ "Not"), 
         Race = as_factor(Race), 
         GenderSex = as_factor(GenderSex), 
         GlobalSouth = as_factor(GlobalSouth)) %>%
  select(team, Race, GenderSex, GlobalSouth, author_team_eth_basic, author_team_gen_basic, corr_author_region3)
write_csv(classification_inter, here("data", "classification_inter.csv"))



review_n = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  group_by(team) %>%
  summarize(n = sum(!is.na(days_sub_to_1stinvite)), 
            median = median(days_sub_to_1stinvite, na.rm = TRUE)) %>%
  mutate(decision = "With peer reviewer(s)", 
         n = paste0("n = ", n, sep = ""), 
         median = paste0("median = ", median, " days", sep = ""))
  
dr_n = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  group_by(team) %>%
  summarize(n = sum(!is.na(days_1stsub_to_desk_reject)), 
            median = median(days_1stsub_to_desk_reject, na.rm = TRUE)) %>%
  mutate(decision = "Desk reject", 
         n = paste0("n = ", n, sep = ""), 
         median = paste0("median = ", median, " days", sep = ""))

new_first_labels = review_n %>%
  bind_rows(dr_n)
write_csv(new_first_labels, here("data", "review_new_first_labels.csv"))

new_first = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  mutate(days = if_else(!is.na(days_sub_to_1stinvite), as.numeric(days_sub_to_1stinvite), as.numeric(days_1stsub_to_desk_reject)), 
         decision = if_else(!is.na(days_sub_to_1stinvite), "With peer reviewer(s)", NA_character_), 
         decision = if_else(!is.na(days_1stsub_to_desk_reject), "Desk reject", decision)) %>%
  select(team, days, decision)
write_csv(new_first, here("data", "review_new_first.csv"))



reject_n = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  group_by(team) %>%
  summarize(n = sum(!is.na(days_1stsub_to_reject_aft_review)), 
            median = median(days_1stsub_to_reject_aft_review, na.rm = TRUE)) %>%
  mutate(decision = "Reject after peer review", 
         n = paste0("n = ", n, sep = ""), 
         median = paste0("median = ", median, " days", sep = ""))

revise_n = newsubs %>%
  group_by(team) %>%
  filter(team == "Mannheim" | team == "Current") %>%
  summarize(n = sum(!is.na(days_1stsub_to_revise)), 
            median = median(days_1stsub_to_revise, na.rm = TRUE)) %>%
  mutate(decision = "R&R after peer review", 
         n = paste0("n = ", n, sep = ""), 
         median = paste0("median = ", median, " days", sep = ""))
  
new_postreview_labels = reject_n %>%
  bind_rows(revise_n) %>%
  mutate(decision = fct_relevel(decision, "Reject after peer review"))
write_csv(new_postreview_labels, here("data", "review_new_postreview_labels.csv"))

new_postreview = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  mutate(days = if_else(!is.na(days_1stsub_to_reject_aft_review), as.numeric(days_1stsub_to_reject_aft_review), as.numeric(days_1stsub_to_revise)), 
         decision = if_else(!is.na(days_1stsub_to_reject_aft_review), "Reject after peer review", NA_character_), 
         decision = if_else(!is.na(days_1stsub_to_revise), "R&R after peer review", decision), 
         decision = fct_relevel(decision, "Reject after peer review")) %>%
  select(team, days, decision)

write_csv(new_postreview, here("data", "review_new_postreview.csv"))



sub_new = sub.all %>%
  filter(team == "Mannheim" | team == "Current") %>%
  filter(revision == 0) %>%
  filter(rev_n_invited != 0) %>%
  filter(!is.na(decision_term_corrected)) %>%
  mutate(year_month = round_date(initial_date_submitted, unit = "month")) %>%
  group_by(year_month) %>%
  summarize(mean_invited = mean(rev_n_invited, na.rm = TRUE),
            se_invited = plotrix::std.error(rev_n_invited, na.rm = TRUE),
            mean_completed = mean(rev_n_completed, na.rm = TRUE),
            se_completed = plotrix::std.error(rev_n_completed, na.rm = TRUE),
            min_invited = mean_invited - 1.96*se_invited,
            max_invited = mean_invited + 1.96*se_invited,
            min_completed = mean_completed - 1.96*se_completed,
            max_completed = mean_completed + 1.96*se_completed,
            med_review = median(days_1stinvite_to_decision, na.rm = TRUE))
write_csv(sub_new, here("data", "review_sub_new.csv"))
sub_review = sub.all %>%
  filter(team == "Mannheim" | team == "Current") %>%
  filter(revision == 0) %>%
  filter(rev_n_invited != 0) %>%
  filter(!is.na(decision_term_corrected)) %>%
  group_by(team) %>%
  summarize(mean_invited = mean(rev_n_invited, na.rm = TRUE), 
            mean_completed = mean(rev_n_completed, na.rm = TRUE), 
            med_review = median(days_1stinvite_to_decision, na.rm = TRUE))
write_csv(sub_review, here("data", "review_sub_review.csv"))



################################################
# table of new submission reviewer characteristics 
revstats_byteam = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  group_by(team) %>%
  summarise(invited = sum(rev_n_invited, na.rm = TRUE), 
            `Overall` = sum(rev_n_completed, na.rm = TRUE)/invited, 
            `Women` = sum(rev_n_women_complete, na.rm = TRUE)/sum(rev_n_women_invited, na.rm = TRUE), 
            `Men` = sum(rev_n_men_completed, na.rm = TRUE)/sum(rev_n_men_invited, na.rm = TRUE), 
            `N. America` = sum(rev_n_northamerica_completed, na.rm=TRUE)/sum(rev_n_northamerica_invited, na.rm=TRUE), 
            `Europe` = sum(rev_n_eur_completed, na.rm=TRUE)/sum(rev_n_eur_invited, na.rm=TRUE), 
            `Other\nregions` = sum(rev_n_other_region_completed, na.rm=TRUE)/sum(rev_n_other_region_invited, na.rm=TRUE)) %>%
  select(-invited)  %>%
  pivot_longer(cols=c(`Overall`:`Other\nregions`)) %>%
  rename("Proportion completed" = value)

revmin_byteam = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  group_by(team) %>%
  summarise(invited = sum(rev_n_invited, na.rm = TRUE), 
            overall = sum(rev_n_completed, na.rm = TRUE)/invited, 
            women = sum(rev_n_women_complete, na.rm = TRUE)/sum(rev_n_women_invited, na.rm = TRUE), 
            men = sum(rev_n_men_completed, na.rm = TRUE)/sum(rev_n_men_invited, na.rm = TRUE), 
            na = sum(rev_n_northamerica_completed, na.rm=TRUE)/sum(rev_n_northamerica_invited, na.rm=TRUE), 
            eur = sum(rev_n_eur_completed, na.rm=TRUE)/sum(rev_n_eur_invited, na.rm=TRUE), 
            other = sum(rev_n_other_region_completed, na.rm=TRUE)/sum(rev_n_other_region_invited, na.rm=TRUE), 
            `Overall` = overall - 1.96 *sqrt(overall*(1-overall)/invited), 
            `Women` = women - 1.96 * sqrt(women*(1-women)/sum(rev_n_women_invited, na.rm = TRUE)), 
            `Men` = men - 1.96 * sqrt(men*(1-men)/sum(rev_n_men_invited, na.rm = TRUE)), 
            `N. America` = na- 1.96 * sqrt(na*(1-na)/sum(rev_n_northamerica_invited, na.rm=TRUE)), 
            `Europe` = eur - 1.96* sqrt(eur*(1-eur)/sum(rev_n_eur_invited, na.rm=TRUE)), 
            `Other\nregions` = other - 1.96 * sqrt(other*(1-other)/sum(rev_n_other_region_invited, na.rm=TRUE))) %>%
  select(-invited, -overall, -women, -men, -na, -eur, -other)  %>%
  pivot_longer(cols=c(`Overall`:`Other\nregions`)) %>%
  rename("min" = value)
revmax_byteam = newsubs %>%
  filter(team == "Mannheim" | team == "Current") %>%
  group_by(team) %>%
  summarise(invited = sum(rev_n_invited, na.rm = TRUE), 
            overall = sum(rev_n_completed, na.rm = TRUE)/invited, 
            women = sum(rev_n_women_complete, na.rm = TRUE)/sum(rev_n_women_invited, na.rm = TRUE), 
            men = sum(rev_n_men_completed, na.rm = TRUE)/sum(rev_n_men_invited, na.rm = TRUE), 
            na = sum(rev_n_northamerica_completed, na.rm=TRUE)/sum(rev_n_northamerica_invited, na.rm=TRUE), 
            eur = sum(rev_n_eur_completed, na.rm=TRUE)/sum(rev_n_eur_invited, na.rm=TRUE), 
            other = sum(rev_n_other_region_completed, na.rm=TRUE)/sum(rev_n_other_region_invited, na.rm=TRUE), 
            `Overall` = overall + 1.96 *sqrt(overall*(1-overall)/invited), 
            `Women` = women + 1.96 * sqrt(women*(1-women)/sum(rev_n_women_invited, na.rm = TRUE)), 
            `Men` = men + 1.96 * sqrt(men*(1-men)/sum(rev_n_men_invited, na.rm = TRUE)), 
            `N. America` = na + 1.96 * sqrt(na*(1-na)/sum(rev_n_northamerica_invited, na.rm=TRUE)), 
            `Europe` = eur + 1.96* sqrt(eur*(1-eur)/sum(rev_n_eur_invited, na.rm=TRUE)), 
            `Other\nregions` = other + 1.96 * sqrt(other*(1-other)/sum(rev_n_other_region_invited, na.rm=TRUE))) %>%
  select(-invited, -overall, -women, -men, -na, -eur, -other)  %>%
  pivot_longer(cols=c(`Overall`:`Other\nregions`)) %>%
  rename("max" = value)

revstats_byteam = revstats_byteam %>%
  left_join(revmin_byteam, by = c("team", "name")) 
revstats_byteam = revstats_byteam %>%
  left_join(revmax_byteam, by = c("team", "name")) %>%
  mutate(name = as_factor(name))

write_csv(revstats_byteam, here("data", "review_revstats_byteam.csv"))



sub.all <- readRDS(here("data","sub.all.rds"))
# filter by date
sub.all = sub.all %>%
  filter(date_rev_sub_corrected <= date("2022-11-30")) %>%
  filter(date_rev_sub_corrected >= date("2012-07-01")) 

monthly = sub.all %>%
  filter(revision == 0) %>%
  relocate(man_num, revision, date_rev_sub_corrected) %>%
  mutate(year = year(date_rev_sub_corrected), 
         month = month(date_rev_sub_corrected), 
         year_month = format_ISO8601(date_rev_sub_corrected, precision = "ym")) %>%
  group_by(year, month, year_month) %>%
  summarize(n = n()) 

by_teams = monthly %>%
  mutate(team = case_when(year_month >= "2012-07-01" & year_month < "2016-07-01" ~ "UNT", 
                          year_month >= "2016-07-01" & year_month < "2020-06-01" ~ "Mannheim", 
                          year_month >= "2020-06-01" & year_month < "2022-11-01" ~ "Current")) %>%
  group_by(team) %>%
  mutate(n_month = row_number()) %>%
  ungroup() %>%
  filter(n_month <30) %>%
  filter(!is.na(team))
write_csv(by_teams, "monthly_byteams.csv")

